﻿using System.IO;
using System.Collections;

namespace QAHelper.Common
{
    public class ExcelReader
    {        
        string in_excel_file = string.Empty;
        string worksheet = string.Empty;

        private System.Data.DataTable ReadInputFromFile()
        {
            ExcelDataReader.ExcelDataReader reader = null;
            FileStream fs = new FileStream(in_excel_file, FileMode.Open, FileAccess.Read, FileShare.Read);
            reader = new ExcelDataReader.ExcelDataReader(fs);
            fs.Close();

            return reader.WorkbookData.Tables[worksheet];
        }

        #region Method to read test scenarios

        /// <summary>
        /// Read the excel file and return the content in a format of ArrayList
        /// </summary>
        /// <param name="test_data_file">Input excel file</param>
        /// <param name="worksheet_name">Worksheet name to read</param>
        /// <param name="inputHeaderCollection">Out parameter for header list</param>
        /// <param name="startIndex">Starting row of test scenarios</param>
        /// <returns>Content in ArrayList format</returns>   
        /// 
        public TestCaseStructure ReadInputFromFile(TestCaseStructure testCaseObj, out ArrayList inputHeaderCollection, int? startIndex)
        {
            ArrayList rowCollection = new ArrayList();
            ArrayList headerCollection = new ArrayList();
            in_excel_file=testCaseObj.TestCaseFileName;
            worksheet=testCaseObj.TestCaseWorkSheetName;

            System.Data.DataTable dt = ReadInputFromFile();

            //Read Header portion of the test case file
            ArrayList summary = ReadFromDataTable(dt, 0, 1);
            testCaseObj.TestCaseSummary = ReadSummaryContent(summary, dt, 1, 6);

            ArrayList testCaseInfo = ReadFromDataTable(dt, 8, 9);
            testCaseObj.TestCaseInfo = ReadSummaryContent(testCaseInfo, dt, 8, 10);
        
            int val = 0;
            if (startIndex != null)
                val = startIndex.Value;

            bool endOfTestCase = false;
            int i = 0;
            for(i=val;i<dt.Rows.Count;i++)
            {
                TestScenario testScenario = new TestScenario();
                if (endOfTestCase)
                    break;
            //foreach (System.Data.DataRow dr in dt.Rows)
            //{              
                System.Data.DataRow dr = dt.Rows[i];
                object[] items = dr.ItemArray;
                if (i == val)
                {
                    foreach (object item in items)
                    {
                        if(item!=System.DBNull.Value)
                            headerCollection.Add(item);
                    }
                    //i++;
                    continue;
                }

                #region Old code (implemented with DictionaryEntry)
                //DictionaryEntry[] dictRows = new DictionaryEntry[headerCollection.Count];

                //for (int j = 0; j < headerCollection.Count; j++)
                //{
                //      
                //    //if (j == 0 && items[j] is System.DBNull)
                //    //{
                //    //    i = -1;
                //    //    break;
                //    //}
                //    //i = val+1;
                //    if (items[0] == System.DBNull.Value) //requried to skip additional rows after test cases
                //        endOfTestCase=true;
                //    if (items[j] == System.DBNull.Value)
                //    {
                //        dictRows[j] = new DictionaryEntry(headerCollection[j], "");
                //    }
                //    else
                //    {
                //        dictRows[j] = new DictionaryEntry(headerCollection[j], items[j]);
                //    }
                //    //InputRowCollection.Add();
                //}
                #endregion

                if (items[0] == System.DBNull.Value) //requried to skip additional rows after test cases
                {
                    endOfTestCase = true;
                    break;
                }
                else
                {
                    int itemArrayIndex = 0;
                    
                    testScenario.TestCaseID = items[itemArrayIndex].ToString();
                    testScenario.SkipExecution = items[itemArrayIndex+1].ToString();
                    testScenario.TestObjective = items[itemArrayIndex + 2].ToString();
                    testScenario.TestSteps = items[itemArrayIndex + 3].ToString();
                    testScenario.TestData = items[itemArrayIndex + 4].ToString();
                    testScenario.ExpectedResult = items[itemArrayIndex + 5].ToString();
                    testScenario.ObservedResult = items[itemArrayIndex + 6].ToString();
                    testScenario.Status = items[itemArrayIndex + 7].ToString();
                    testScenario.CommentBUGID = items[itemArrayIndex + 8].ToString();

                    if (!endOfTestCase)
                        rowCollection.Add(testScenario);
                }
            }
            inputHeaderCollection = headerCollection;
            testCaseObj.TestScenarios = rowCollection;
            testCaseObj.TestCaseFooter = ReadFromDataTable(dt,i,i+12);

            return testCaseObj;
        }

        #endregion

        public ArrayList ReadFromDataTable(System.Data.DataTable dt, int start_row, int end_row)
        {
            ArrayList tempList = new ArrayList();
            for (int i = start_row; i < end_row; i++)
            {
                System.Data.DataRow dr = dt.Rows[i];
                object[] items = dr.ItemArray;
                foreach (object item in items)
                {
                    if (item != System.DBNull.Value)
                        tempList.Add(item);
                }
            }

            return tempList;

        }

        public ArrayList ReadSummaryContent(ArrayList summaryContent, System.Data.DataTable dt, int start_row, int end_row)
        {
            ArrayList tempList = new ArrayList();
            for (int i = start_row; i < end_row; i++)
            {
                System.Data.DataRow dr = dt.Rows[i];
                object[] items = dr.ItemArray;
                string str = string.Empty;
                int j = 0;
                //foreach (object item in items)
                while(j<6)
                {
                    object item = items[j];

                    if (item != System.DBNull.Value)
                    {
                        if (string.IsNullOrEmpty(str))
                            str += (string)item;
                        else
                            str += ";" + (string)item;

                        if (str.Contains(":"))
                        {                            
                            System.DateTime val = System.DateTime.Now;
                            if (System.DateTime.TryParse(items[j + 1].ToString(), out val))
                            {
                                item = System.Convert.ToDateTime(item);
                                item = val;
                                str += "+" + item;
                            }
                            else
                            {
                                str += "+" + items[j + 1].ToString();
                            }
                            j += 2;
                        }
                        else
                        {
                            str += string.Empty;
                            j++;
                        }
                        //summaryContent.Add(str);
                    }
                    else
                    {                        
                        j++;
                    }
                }
                summaryContent.Add(str);
            }

            return summaryContent;

        }

        public ArrayList ReadInputFromFile(string test_data_file, string worksheet_name, out ArrayList inputHeaderCollection)
        {
            ArrayList rowCollection = new ArrayList();
            ArrayList headerCollection = new ArrayList();
            in_excel_file=test_data_file;
            worksheet=worksheet_name;

            System.Data.DataTable dt = ReadInputFromFile();

            int i = 0;
            foreach (System.Data.DataRow dr in dt.Rows)
            {
                object[] items = dr.ItemArray;
                if (i == 0)
                {
                    foreach (object item in items)
                    {
                        headerCollection.Add(item);
                    }
                    i++;
                    continue;
                }
                DictionaryEntry[] dictRows = new DictionaryEntry[items.Length];

                for (int j = 0; j < items.Length; j++)
                {
                    if (j == 0 && items[j] is System.DBNull)
                    {
                        i = -1;
                        break;
                    }
                    i = 1;
                    dictRows[j] = new DictionaryEntry(headerCollection[j], items[j]);
                    //InputRowCollection.Add();
                }
                if (i > 0)
                    rowCollection.Add(dictRows);
            }
            inputHeaderCollection = headerCollection;
            return rowCollection;
        }

    }
}
